﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace SisLegal.Model
{    
    public class CrimeSpecific
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string NameSubGenerico { get; set; }
        public string NameGenerico { get; set; }
        public int IDSubGenerico { get; set; }
        public int IDGenerico { get; set; }
        public int Article { get; set; }

        public CrimeSpecific()
        {
            this.ID = 0;
            this.Name = "";
            this.NameSubGenerico = "";
            this.NameGenerico = "";
            this.IDSubGenerico = 0;
            this.Article = 0;
        }
    }
    public class CrimeSpecificManager
    {
        public List<CrimeSpecific> GetAll()
        {
            List<CrimeSpecific> result = new List<CrimeSpecific>();
            string sqlQuery = String.Format("SELECT * FROM CrimeSpecific ORDER BY ID");
            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            SqlDataReader dataReader = command.ExecuteReader();
            CrimeSpecific process = null;
            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    process = new CrimeSpecific();
                    process.ID = Convert.ToInt32(dataReader["ID"]);
                    process.Name = dataReader["Name"].ToString();
                    result.Add(process);
                }
            }
            return result;
        }
        public Int32 Count()
        {
            List<CrimeSpecific> result = new List<CrimeSpecific>();
            string sqlQuery = String.Format("  SELECT COUNT(ID) AS total FROM CrimeSpecific");
            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            command.CommandType = CommandType.Text;
            Int32 countStart = (Int32)command.ExecuteScalar();
            return countStart;
        }

        public List<CrimeSpecific> Paginate(Int32 pag)
        {
            List<CrimeSpecific> result = new List<CrimeSpecific>();
            string sqlQuery = String.Format("SELECT * FROM (" +
                "SELECT ROW_NUMBER() OVER(ORDER BY cs.ID) AS NUMBER, cs.ID, cs.Name,  cs.Article, cs.IDCrimeSubGeneric, csg.Name as CrimeSubGeneric, cg.Name as CrimeGeneric FROM CrimeSpecific cs " +
                "INNER JOIN dbo.CrimeSubGeneric csg ON cs.IDCrimeSubGeneric = csg.ID "+
                "INNER JOIN dbo.CrimeGeneric cg ON csg.IDCrimeGeneric = cg.ID " +
                ")as tbl " +
                "WHERE NUMBER BETWEEN((" + Convert.ToString(pag) + "-1) * (10 +1)) AND (" + Convert.ToString(pag) + " * 10) ");

            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            SqlDataReader dataReader = command.ExecuteReader();
            CrimeSpecific process = null;
            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    process = new CrimeSpecific();
                    process.ID = Convert.ToInt32(dataReader["ID"]);
                    process.Name = dataReader["Name"].ToString();
                    process.Article = Convert.ToInt32(dataReader["Article"]);
                    process.IDSubGenerico = Convert.ToInt32(dataReader["IDCrimeSubGeneric"]);
                    process.NameSubGenerico = dataReader["CrimeSubGeneric"].ToString();
                    process.NameGenerico = dataReader["CrimeGeneric"].ToString();
                    result.Add(process);
                }
            }
            return result;
        }

        public int Save(CrimeSpecific obj)
        {
            string createQuery = String.Format("Insert into CrimeSpecific (Name, Article, IDCrimeSubGeneric) Values('{0}','{1}','{2}');"
                + "Select @@Identity", obj.Name, obj.Article, obj.IDSubGenerico);

            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = null;

            command = new SqlCommand(createQuery, connection);

            int saveID = 0;
            try
            {
                var commandResult = command.ExecuteScalar();
                if (commandResult != null)
                {
                    saveID = Convert.ToInt32(commandResult);
                }
            }
            catch (Exception ex)
            {

            }

            command.Dispose();
            connection.Close();
            connection.Dispose();

            return saveID;
        }

        public int Update(CrimeSpecific obj)
        {
            string updateQuery = String.Format("Update CrimeSpecific SET Name='{0}' Where ID = {1};",
               obj.Name, obj.ID);

            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = null;
            command = new SqlCommand(updateQuery, connection);

            int saveID = 0;
            try
            {
                var commandResult = command.ExecuteScalar();
                if (commandResult != null)
                {
                    saveID = Convert.ToInt32(commandResult);
                }
                else
                {
                    saveID = obj.ID;
                }
            }
            catch (Exception ex)
            {

            }
            command.Dispose();
            connection.Close();
            connection.Dispose();

            return saveID;
        }

        public CrimeSpecific GetById(int Id)
        {
            CrimeSpecific result = new CrimeSpecific();
            string sqlQuery = String.Format("select * from CrimeSpecific where ID={0}", Id);
            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            SqlDataReader dataReader = command.ExecuteReader();

            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    result.ID = Convert.ToInt32(dataReader["ID"]);
                    result.Name = dataReader["Name"].ToString();
                }
            }

            return result;
        }

        public CrimeSpecific GetByArticle(int articleId)
        {
            CrimeSpecific result = new CrimeSpecific();
            string sqlQuery = String.Format("SELECT ROW_NUMBER() OVER(ORDER BY cs.ID) AS NUMBER, cs.ID, cs.Name,  cs.Article, cs.IDCrimeSubGeneric, csg.Name as CrimeSubGeneric, cg.Name as CrimeGeneric FROM CrimeSpecific cs " +
                "INNER JOIN dbo.CrimeSubGeneric csg ON cs.IDCrimeSubGeneric = csg.ID "+
                "INNER JOIN dbo.CrimeGeneric cg ON csg.IDCrimeGeneric = cg.ID where cs.Article ={0}", articleId);
            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            SqlDataReader dataReader = command.ExecuteReader();

            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    result.ID = Convert.ToInt32(dataReader["ID"]);
                    result.Name = dataReader["Name"].ToString();
                    result.Name = dataReader["Name"].ToString();
                    result.Article = Convert.ToInt32(dataReader["Article"]);
                    result.IDSubGenerico = Convert.ToInt32(dataReader["IDCrimeSubGeneric"]);
                    result.NameSubGenerico = dataReader["CrimeSubGeneric"].ToString();
                    result.NameGenerico = dataReader["CrimeGeneric"].ToString();
                }
            }

            return result;
        }

        public bool Delete(int articleId)
        {
            bool result = false;
            string sqlQuery = String.Format("delete from CrimeSpecific where ID = {0}", articleId);

            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            int rowsDeletedCount = command.ExecuteNonQuery();
            if (rowsDeletedCount != 0)
                result = true;
            command.Dispose();
            connection.Close();
            connection.Dispose();
            return result;
        }
    }
}